package DBOpt;
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;

import org.w3c.dom.*;
import javax.xml.parsers.*;
import java.sql.*;
import java.util.*;

import twitter4j.*;

public class selectNewUserApp {

 Connection con;
 DocumentBuilderFactory factory;
 ArrayList<String[]> SQLList;
 private static String outputfile = "../output/meas/newuser.txt";
  public boolean Connect (String host, String user, String password, String dbname)
  {
	  factory = DocumentBuilderFactory.newInstance();  
	  try {
	      
	      Class.forName("com.mysql.jdbc.Driver").newInstance();

	    }
	    catch( Exception e ) {
	      System.out.println("Failed to load mySQL driver.");
	      return false;
	    }
	    try {
	    	
		    String url = "jdbc:mysql://"+host.trim()+"/"+dbname.trim();
		     
		 con = DriverManager.getConnection(url, user, password);
         //con = DriverManager.getConnection(url);
	      System.out.println("Connect Succ!");
	      return true;
	    }
	    catch( Exception e ) {
	      e.printStackTrace();
	      return false;
	    }
	    
  }
  
  private void setSQLlist(String tablename)
  {
	  SQLList = new ArrayList<String[]>();
	  SQLList.clear();
	  
	  String sqlpair[] = new String[3];
	  sqlpair[0]= "select RES.createdAtDate, count(*) from (select createdAtDate,source from timeline group by source) as RES WHERE MONTH(createdAtDate)=9 group by createdAtDate;";
	  sqlpair[1]= "Count the number of new source per day";
	  sqlpair[2]= "Date\t#NewSource";
	  SQLList.add(sqlpair);
	  
	  sqlpair = new String[3];
	  sqlpair[0]= "SELECT source, count(*) from timeline WHERE MONTH(createdAtDate)=9 group by source order by count(*) DESC LIMIT 0,10;";
	  sqlpair[1]= "Count #Msg per source for top 10 sources";
	  sqlpair[2]= "Source\t#Source";
	  SQLList.add(sqlpair);
	  
	  
	  sqlpair = new String[3];
	  sqlpair[0]= "SELECT userName,count(*) from timeline WHERE MONTH(createdAtDate)=9 group by userID order by count(*) DESC LIMIT 0,20 ;";
	  sqlpair[1]= "#Msg from Top 20 users";
	  sqlpair[2]= "UserName\t#MSG";
	  SQLList.add(sqlpair);
	  
	  sqlpair = new String[3];
	  sqlpair[0]= "SELECT userLocation,count(*) from timeline WHERE MONTH(createdAtDate)=9 group by userLocation order by count(*) DESC LIMIT 0,20 ;";
	  sqlpair[1]= "#Msg from Top 20 locations";
	  sqlpair[2]= "Location\t#MSG";
	  SQLList.add(sqlpair);
	  
	  sqlpair = new String[3];
	  sqlpair[0]= "SELECT userLocation, source,count(*) from timeline WHERE MONTH(createdAtDate)=9 group by userLocation,source order by count(*) DESC LIMIT 0,20;";
	  sqlpair[1]= "#Msg per location per source (top20)";
	  sqlpair[2]= "Location\tSource\t#MSG";
	  SQLList.add(sqlpair);
	  
	  	  
  }
  public void selectSource(String tablename)
  {
	
	setSQLlist(tablename);  
	  try{
		  PrintStream output = new PrintStream (new FileOutputStream(outputfile));
		  output.println(" ----- Timeline Data Stat on September 2008----- ");
		  
		  Iterator<String[]> iter = SQLList.iterator();
          
          while (iter.hasNext())
          {
        	  String[] sqlpair = iter.next();
        	  
        	  Statement stmt = con.createStatement();
        	  ResultSet rs = stmt.executeQuery(sqlpair[0]);
    		  output.println(" ----- "+sqlpair[1]+" ----- ");
    		  //output.println(sqlpair[0]);
    		  
    		  System.out.println(" ----- "+sqlpair[0]+" ----- ");
    		  ResultSetMetaData rsmd = rs.getMetaData();
			  int numCols = rsmd.getColumnCount();
			  /*for (int i=1;i<=numCols;i++)
			  {
				  output.print(rsmd.getColumnName(i)+"\t");  
				  //System.out.print(rsmd.getColumnName(i)+"\t");
			  }*/
			  //System.out.println();
			  output.println();
			  output.println(sqlpair[2]);
    		  while (rs.next()) {
    			 for (int i=1;i<=numCols;i++)
    			   {
    				   //System.out.print(rs.getString(i)+"\t");
    				   output.print(rs.getString(i).trim()+"\t");
    			   }
    			output.println();
    			}
    		  rs.close();
    		  output.println();
    		  output.println();
  
          }
		  
			output.close();
  
	  }
		
	  catch( Exception e ) {
	      e.printStackTrace();
	      return ;
	    }
	  
  }
  
  public static void main(String args[]) {
	
	if (args.length < 5)
	{
		System.out.println("Usage: selectdata <Source file directoy> <hostname> <username> <password> <database name> <table name>");
		System.exit(0) ;
	}
	try{  
	
	String host = args[0];
	String user = args[1];
	String password = args[2];
	String dbname = args[3];
	String tablename = args[4];

	selectNewUserApp selectapp = new selectNewUserApp();
	boolean ret = selectapp.Connect (host,user,password,dbname);
	if (!ret)
	{
		System.out.println("Init Failed!");
		System.exit (0);
		
	}
    selectapp.selectSource(tablename);
    selectapp.con.close();
    System.out.println ("Done!");
	}
      catch( Exception e ) 
      {
         e.printStackTrace();
       }
  }
  
}
